package cn.kain.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.kain.dao.GoodsTypeDao;
import cn.kain.entity.GoodsType;
import cn.kain.util.DBTool;

/**
 * @author kai
 * @email kain.wong@foxmail.com
 * @Data 2020年1月7日 下午5:54:11
 * @Description TODO
 * 
 */
public class GoodsTypeDaoImpl implements GoodsTypeDao {

	private Connection conn = null;
	private PreparedStatement psmt = null;
	
	@Override
	public List<GoodsType> getAllType(Integer typeId) {
		String sql = "SELECT * FROM goods_type ";
		if(getAllTypeID().contains(typeId)) {
			sql+="where type_id="+typeId;
		}else {
			sql+="ORDER BY type_id";
		}
		
		List<GoodsType> list = new ArrayList<GoodsType>();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()) {
				GoodsType t = new GoodsType();
				t.setId(rs.getInt("id"));
				t.setTypeId(rs.getInt("type_id"));
				t.setTypeName(rs.getString("type_name"));
				t.setTypeNameDetail(rs.getString("type_name_detail"));
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
		return list;
	}

	@Override
	public GoodsType selectById(int id) {
		String sql = "select *  from goods_type where id=?";
		GoodsType t = new GoodsType();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs = psmt.executeQuery();
			if(rs.next()) {
				t.setId(rs.getInt("id"));
				t.setTypeId(rs.getInt("type_id"));
				t.setTypeName(rs.getString("type_name"));
				t.setTypeNameDetail(rs.getString("type_name_detail"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
		return t;
	}

	@Override
	public void addType(GoodsType t) {
		String sql = "INSERT INTO goods_type (type_id,type_name,type_name_detail,note) values (?,?,?,?) ";
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, t.getTypeId());
			psmt.setString(2, t.getTypeName());
			psmt.setString(3, t.getTypeNameDetail());
			psmt.setString(4, t.getNote());
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}

	}

	@Override
	public void deleteType(int id) {
		String sql = "DELETE FROM goods_type where id=?";
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
	}
	
	public List<String> getAllTypeName(){
		String sql = "SELECT DISTINCT type_name FROM goods_type";
		List<String> list = new ArrayList<String>();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()) {
				list.add(rs.getString("type_name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
		return list;
	}
	
	/**
	 * 获取商品类型表中所有的类型id
	 * @return
	 */
	public List<Integer> getAllTypeID(){
		String sql = "SELECT DISTINCT type_id FROM goods_type";
		List<Integer> list = new ArrayList<Integer>();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()) {
				list.add(rs.getInt("type_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
		return list;
	}

	@Override
	public List<String> getSecondTypeName(String typeName) {
		String sql = "SELECT type_name_detail FROM goods_type WHERE type_name=?";
		List<String> list = new ArrayList<String>();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, typeName);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()) {
				list.add(rs.getString("type_name_detail"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
		return list;
	}

	@Override
	public int getTypeId(String typeName, String typeName2) {
		String sql = "SELECT id FROM goods_type where type_name=? and type_name_detail=?";
		int id = 0;
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, typeName);
			psmt.setString(2, typeName2);
			ResultSet rs = psmt.executeQuery();
			if(rs.next()) {
				id = rs.getInt("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
		return id;
	}


}
